package com.baixun.gassystem.service;


import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.json.JSONArray;
import org.json.JSONObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @program: 上海燃气驾驶舱
 * @description: 负责处理数据和保存到数据库
 * @author: yesong
 **/
@Service
public class DatabaseService {

    @Autowired
    private ApiService apiService;

    @Autowired
    private SysUserService sysUserService;

    public DatabaseService() throws SQLException {
    }


    // 获取数据库连接
    private static Connection getDatabaseConnection() throws SQLException {
        String url = "jdbc:mysql://gz-cdb-a1k9mjhj.sql.tencentcdb.com:29414/shanghai-gas";
        String user = "root";
        String password = "clRDS@1010&&";
        return DriverManager.getConnection(url, user, password);
    }

    //树形结构
    @Scheduled(fixedRate = 86400000) //
    public void downloadAndInsertData() {
        try {
            // 获取根节点数据并解析出 data 字段
            String rootDataString = apiService.treeSearch();
            JSONObject responseObject = new JSONObject(rootDataString);
            JSONObject rootNode = responseObject.getJSONObject("data");

            // 调用 insertTreeNode 递归插入根节点及其子节点
            insertTreeNode(rootNode);

        } catch (SQLException e) {
            e.printStackTrace(); // 捕获异常，打印错误信息
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 插入数据到数据库，保留节点是否存在的判断逻辑
    private void insertTreeNode(JSONObject node) throws SQLException {
        // 获取数据库连接
        try (Connection conn = getDatabaseConnection()) {
            // 检查节点是否已经存在
            String checkSql = "SELECT COUNT(*) FROM `sys_video_message` WHERE `parent_id` = ?";
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                checkStmt.setLong(1, node.getLong("id"));
                ResultSet rs = checkStmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    // 如果节点已经存在，跳过插入操作
                    System.out.println("节点已存在，跳过插入：" + node.getLong("id"));
                    return;
                }
            }

            // 准备插入节点的SQL语句
            String sql = "INSERT INTO `sys_video_message` (`parent_id`, `type`, `point_count`, `online_point_count`, `point_status`) " +
                    "VALUES (?, ?, ?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setLong(1, node.getLong("id"));
                stmt.setString(2, node.getString("type"));
                stmt.setInt(3, node.getInt("pointCount"));
                stmt.setInt(4, node.getInt("onlinePointCount"));
                stmt.setString(5, node.getString("pointStatus"));

                // 执行插入
                stmt.executeUpdate();
                System.out.println("插入节点成功：" + node.getLong("id"));
            }

            // 如果有子节点，递归处理子节点
            if (node.optBoolean("hasChildren", false)) {
                JSONArray children = node.optJSONArray("children");
                if (children != null) {
                    for (int i = 0; i < children.length(); i++) {
                        insertTreeNode(children.getJSONObject(i)); // 递归插入子节点
                    }
                }
            }
        }
    }


    /**
     * 获取部门数据并保存到数据库
     *
     * @throws Exception
     */
    // 定时任务：每24小时执行一次数据保存
    @Scheduled(fixedRate = 86400000) // 24小时=86400000毫秒
    public void saveDepartmentsToDatabase() throws Exception {
        // 获取接口数据
        String response = apiService.getDepartments();

        // 解析返回的 JSON 数据
        JSONObject jsonResponse = new JSONObject(response);
        JSONArray dataArray = jsonResponse.getJSONArray("data");

        // 获取数据库连接
        Connection connection = getDatabaseConnection();

        // 插入部门数据的SQL语句
        String insertSql = "INSERT INTO sys_department (id, parent_id, name, leaf, deleted, create_time) "
                + "VALUES (?, ?, ?, ?, 0, CURRENT_TIMESTAMP)";

        // SQL查询判断是否存在该id的语句
        String selectSql = "SELECT COUNT(*) FROM sys_department WHERE id = ?";

        try (PreparedStatement insertStmt = connection.prepareStatement(insertSql);
             PreparedStatement selectStmt = connection.prepareStatement(selectSql)) {

            // 遍历JSON数据，逐条插入数据库
            for (int i = 0; i < dataArray.length(); i++) {
                JSONObject department = dataArray.getJSONObject(i);

                // 读取JSON对象中的字段
                Long id = department.getLong("id");
                Long parentId = department.getLong("parentId");
                String name = department.getString("name");
                Boolean leaf = department.getBoolean("leaf");

                // 查询数据库中是否已经存在该id - 核心改动在此！
                selectStmt.setLong(1, id);  // 在循环中，每次处理每条数据前执行查询
                ResultSet rs = selectStmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    // 如果已存在，跳过该条记录
                    continue;
                }

                // 设置PreparedStatement的参数
                insertStmt.setLong(1, id);
                insertStmt.setLong(2, parentId);
                insertStmt.setString(3, name);
                insertStmt.setBoolean(4, leaf);

                // 执行插入操作
                insertStmt.addBatch();
            }

            // 批量执行插入操作
            insertStmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("Error while inserting department data into the database.", e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 获取人员数据并保存到数据库
     *
     * @throws Exception
     */
    //@Scheduled(fixedRate = 86400000) // 24小时=86400000毫秒
    public void saveUsersFromAll() throws Exception {
        // 获取所有部门的用户列表
        List<com.alibaba.fastjson.JSONObject> allUsers = apiService.getUsersFromAllDepartments();

        // 获取数据库连接
        Connection connection = getDatabaseConnection();

        // 插入用户数据的SQL语句（保留了department_id字段）
        String insertUserSql = "INSERT INTO sys_department_user (department_id, user_id, deleted, create_time) "
                + "VALUES (?, ?, 0, CURRENT_TIMESTAMP)";

        // SQL查询判断是否存在该user_id的语句
        String checkUserSql = "SELECT COUNT(*) FROM sys_department_user WHERE user_id = ?";

        try (PreparedStatement insertUserStmt = connection.prepareStatement(insertUserSql);
             PreparedStatement checkUserStmt = connection.prepareStatement(checkUserSql)) {

            // 遍历用户列表，逐条插入数据库
            for (com.alibaba.fastjson.JSONObject user : allUsers) {
                Long userId = user.getLong("userId");
                Long departmentId = user.getLong("departmentId"); // 保留department_id

                // 查询数据库中是否已经存在该 user_id
                checkUserStmt.setLong(1, userId);
                ResultSet userRs = checkUserStmt.executeQuery();
                if (userRs.next() && userRs.getInt(1) > 0) {
                    // 如果已存在，跳过该条记录
                    continue;
                }

                // 设置PreparedStatement的参数
                insertUserStmt.setLong(1, departmentId); // 保留department_id
                insertUserStmt.setLong(2, userId);


                // 执行插入操作
                insertUserStmt.addBatch();
            }

            // 批量执行插入操作
            insertUserStmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("Error while inserting department users data into the database.", e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 将数据添加到数据库sys——points表中
     */
    // TODO 每次去访问点位接口，都更新一次数据，有可能设备状态会产生修改
    @Scheduled(fixedRate = 3600000) // 3600000 毫秒等于 1 小时
    public void updatePointsToDatabase() throws Exception {
        // 获取点位列表
        String pointsListResponse = apiService.getPointsList();
        System.out.println("获取点位列表"+pointsListResponse);
        // 解析点位列表的 JSON 响应
        JSONObject jsonResponse = new JSONObject(pointsListResponse);
        JSONArray pointsData = jsonResponse.getJSONArray("data");

        // 获取数据库连接
        Connection connection = getDatabaseConnection();

        // SQL 查询是否存在 pointId
        String checkSql = "SELECT COUNT(*) FROM sys_point WHERE pointId = ?";
        // SQL 更新或插入的语句
        String updateSql = "UPDATE sys_point SET pointName = ?, areaId = ?, paasChannelId = ?, parentDeviceName = ?, areaPath = ?, shareGbId = ?, deviceType = ?, point_status = ?  WHERE pointId = ?";
        String insertSql = "INSERT INTO sys_point (pointId, pointName, areaId, paasChannelId, parentDeviceName, areaPath, shareGbId, deviceType, point_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
        // SQL 检查和插入 areaId 的语句
        String checkAreaSql = "SELECT COUNT(*) FROM sys_area WHERE areaId = ?";
        String insertAreaSql = "INSERT INTO sys_area (areaId, name) VALUES (?, ?)";

        try (PreparedStatement checkStmt = connection.prepareStatement(checkSql);
             PreparedStatement updateStmt = connection.prepareStatement(updateSql);
             PreparedStatement insertStmt = connection.prepareStatement(insertSql);
             PreparedStatement checkAreaStmt = connection.prepareStatement(checkAreaSql);
             PreparedStatement insertAreaStmt = connection.prepareStatement(insertAreaSql)) {

            // 遍历获取的点位数据，提取所需字段
            for (int i = 0; i < pointsData.length(); i++) {
                JSONObject point = pointsData.getJSONObject(i);

                // 提取所需字段
                Long pointId = point.getLong("pointId");
                String pointName = point.getString("pointName");
                Long areaId = point.getLong("areaId");
                Long paasChannelId = point.optLong("paasChannelId", 0);
                String parentDeviceName = point.optString("parentDeviceName", null);
                String areaPath = point.optString("areaPath", null);
                String shareGbId = point.optString("shareGbId", null);
                String deviceType = point.optString("deviceType", null);
                String pointStatus = point.optString("pointStatus", null);

                // 检查 areaId 是否存在于 sys_area 表中
                checkAreaStmt.setLong(1, areaId);
                ResultSet areaRs = checkAreaStmt.executeQuery();
                if (areaRs.next() && areaRs.getInt(1) == 0) {
                    // 如果不存在，则插入 areaId 到 sys_area 表
                    insertAreaStmt.setLong(1, areaId);
                    insertAreaStmt.setString(2, "默认区域名称"); // 如果有具体的区域名称，可以替换实际值
                    insertAreaStmt.executeUpdate();
                }

                // 检查数据库中是否存在该 pointId
                checkStmt.setLong(1, pointId);
                ResultSet rs = checkStmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    // 如果存在，则更新记录
                    updateStmt.setString(1, pointName);
                    updateStmt.setLong(2, areaId);
                    updateStmt.setLong(3, paasChannelId);
                    updateStmt.setString(4, parentDeviceName);
                    updateStmt.setString(5, areaPath);
                    updateStmt.setString(6, shareGbId);
                    updateStmt.setString(7, deviceType);
                    updateStmt.setString(8, pointStatus);
                    updateStmt.setLong(9, pointId);
                    updateStmt.addBatch();
                } else {
                    // 如果不存在，则插入记录
                    insertStmt.setLong(1, pointId);
                    insertStmt.setString(2, pointName);
                    insertStmt.setLong(3, areaId);
                    insertStmt.setLong(4, paasChannelId);
                    insertStmt.setString(5, parentDeviceName);
                    insertStmt.setString(6, areaPath);
                    insertStmt.setString(7, shareGbId);
                    insertStmt.setString(8, deviceType);
                    insertStmt.setString(9, pointStatus);
                    insertStmt.addBatch();
                }
            }

            // 执行批量更新和插入操作
            updateStmt.executeBatch();
            insertStmt.executeBatch();

        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("Error while updating or inserting point data in database.", e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


/**
 * 把位置信息加入点位表
 */
@Scheduled(fixedRate = 3600000) // 3600000 毫秒等于 1 小时
public void updatepointLocation() throws Exception {
    // 获取点位列表
    String pointsListResponse = apiService.getPointPosition();
    // 创建一个 Map 来存储每个点的 geo 和 tags 数据
    Map<String, Map<String, Object>> geoAndTagsDataMap = new HashMap<>();


    // 解析点位列表的 JSON 响应
    JSONObject jsonResponse = new JSONObject(pointsListResponse);
    JSONArray pointsData = jsonResponse.getJSONArray("data");
    // 遍历数组中的每个对象
    for (int i = 0; i < pointsData.length(); i++) {
        JSONObject pointData = pointsData.getJSONObject(i); // 获取每个对象

        // 创建一个 Map 来存储每个点的数据
        Map<String, Object> pointDataMap = new HashMap<>();

        // 检查该对象是否包含 'geo' 键
        if (pointData.has("geo")) {
            // 获取 'geo' 对象
            JSONObject geo = pointData.getJSONObject("geo");

            // 创建一个 Map 来存储 geo 中的数据
            Map<String, Object> geoMap = new HashMap<>();
            geoMap.put("longitude", geo.getDouble("longitude"));
            geoMap.put("latitude", geo.getDouble("latitude"));
            geoMap.put("location", geo.getString("location"));

            // 将 geoMap 存入 pointDataMap
            pointDataMap.put("geo", geoMap);
        }

        // 检查该对象是否包含 'tags' 数组
        if (pointData.has("tags")) {
            JSONArray tagsArray = pointData.getJSONArray("tags");

            // 创建一个 List 来存储 tags 数据
            List<Map<String, Object>> tagsList = new ArrayList<>();

            // 遍历 tags 数组
            for (int j = 0; j < tagsArray.length(); j++) {
                JSONObject tag = tagsArray.getJSONObject(j);

                // 创建一个 Map 来存储每个 tag 的信息
                Map<String, Object> tagMap = new HashMap<>();
                tagMap.put("type", tag.getString("type"));
                tagMap.put("tagNames", tag.getJSONArray("tagNames").toList());

                // 将 tagMap 存入 tagsList
                tagsList.add(tagMap);
            }

            // 将 tagsList 存入 pointDataMap
            pointDataMap.put("tags", tagsList);
        }

        // 将 pointDataMap 存入 geoAndTagsDataMap
        geoAndTagsDataMap.put("point" + (i + 1), pointDataMap);
    }


    // 获取数据库连接
    Connection connection = getDatabaseConnection();

    // SQL 查询是否存在 pointId
    String checkSql = "SELECT COUNT(*) FROM sys_point WHERE pointId = ?";
    // SQL 更新或插入的语句
    String updateSql = "UPDATE sys_point SET  longitude = ?, latitude = ?, location = ?,tags=? WHERE pointId = ?";

    // 使用 PreparedStatement 来执行 SQL
    PreparedStatement preparedStatement = null;

    // 遍历 geoAndTagsDataMap，执行更新操作
    for (Map.Entry<String, Map<String, Object>> entry : geoAndTagsDataMap.entrySet()) {
        Map<String, Object> pointDataMap = entry.getValue();

        // 假设 pointDataMap 包含 'pointId' 和 'geo' 信息
        long pointId = (long) pointDataMap.get("pointId");
        Map<String, Object> geo = (Map<String, Object>) pointDataMap.get("geo");
        String location = (String) geo.get("location");
        double longitude = (double) geo.get("longitude");
        double latitude = (double) geo.get("latitude");

        // 获取 tags 数据并转换为 JSON 字符串
        List<Map<String, Object>> tags = (List<Map<String, Object>>) pointDataMap.get("tags");
        String tagsJson = new ObjectMapper().writeValueAsString(tags); // 使用 Jackson 将 tags 转为 JSON 字符串

        // 查询是否存在该 pointId
        preparedStatement = connection.prepareStatement(checkSql);
        preparedStatement.setLong(1, pointId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int count = resultSet.getInt(1); // 获取查询结果，判断是否存在

        // 如果存在，执行更新操作
        if (count > 0) {
            preparedStatement = connection.prepareStatement(updateSql);
            preparedStatement.setDouble(1, longitude);
            preparedStatement.setDouble(2, latitude);
            preparedStatement.setString(3, location);
            preparedStatement.setString(4, tagsJson);
            preparedStatement.setLong(5, pointId);
            preparedStatement.executeUpdate();
        }
        // 如果没有该 pointId，则跳过，不做任何操作
    }
}



    //部门id修改
    //更新文档
    //测试数据
    //token时效性

}